package com.oocl.ita.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.ita.dao.ChecksDao;
import com.oocl.ita.dao.ShopDao;
import com.oocl.ita.vo.check.CheckShopModel;
import com.oocl.ita.vo.check.ChecksShopModel;
import com.oocl.ita.util.DBUtil;

public class ShopDaoImpl implements ShopDao {

	public List<CheckShopModel> showManageShops() {
		String sql = "select * from shop,merchant where merchant.merchantid=shop.merchantid and available in (1,-2)";
		Connection connection = null;
		PreparedStatement statement = null;
		List<CheckShopModel> CheckShopModels = new ArrayList<CheckShopModel>();
		ResultSet resultSet = null;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int shopId = resultSet.getInt("shopId");
				String shopName = resultSet.getString("shopName");
				String shopNumber = resultSet.getString("shopNumber");
				String idNumber = resultSet.getString("idNumber");
				String legalName = resultSet.getString("legalName");
				String address = resultSet.getString("address");
				String signTime = resultSet.getString("signTime");
				String describe = resultSet.getString("describe");
				int available = resultSet.getInt("available");
				String sPhone = resultSet.getString("sPhone");
				CheckShopModel checkShopModel = new CheckShopModel(shopId, shopName, shopNumber, idNumber, legalName,
						address, signTime, describe, available, sPhone);
				CheckShopModels.add(checkShopModel);
			}
		} catch (SQLException e) {
			System.out.println("select error");
		} finally {
			DBUtil.free(connection, statement, resultSet);
		}
		return CheckShopModels;
	}

	public List<ChecksShopModel> showCheckShops() {
		String sql = "select shop.shopId,shopName,shopNumber,idNumber,legalName,address,signTime,describe,available,sPhone,checksId"
				+ " from shop,merchant,checks "
				+ "where merchant.merchantid=shop.merchantid and shop.shopid=checks.shopid "
				+ "and available =? and rownum<=10";
		Connection connection = null;
		PreparedStatement statement = null;
		List<ChecksShopModel> ChecksShopModels = new ArrayList<ChecksShopModel>();
		ResultSet resultSet = null;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			statement.setInt(1, 0);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int shopId = resultSet.getInt("shopId");
				String shopName = resultSet.getString("shopName");
				String shopNumber = resultSet.getString("shopNumber");
				String idNumber = resultSet.getString("idNumber");
				String legalName = resultSet.getString("legalName");
				String address = resultSet.getString("address");
				String signTime = resultSet.getString("signTime");
				String describe = resultSet.getString("describe");
				int available = resultSet.getInt("available");
				String sPhone = resultSet.getString("sPhone");
				int checksId = resultSet.getInt("checksId");
				ChecksShopModel checksShopModel = new ChecksShopModel(shopId, shopName, shopNumber, idNumber, legalName,
						address, signTime, describe, available, sPhone,checksId);
				ChecksShopModels.add(checksShopModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.free(connection, statement, resultSet);
		}
		return ChecksShopModels;
	}

	// wait for extension 2016/07/24 burgess Lai
	public int updateShopToPass(int shopId) {
		String sql = "update shop set available = 1 where shopId=" + shopId;
		Connection connection = null;
		PreparedStatement statement = null;
		int b = 0;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			b = statement.executeUpdate();
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
					b = -1;
				} catch (SQLException e1) {
					b = -2;
				}
			} else {
				b = -3;
			}
		} finally {
			DBUtil.free(connection, statement, null);
		}
		return b;
	}

	// wait for extension 2016/07/24 burgess Lai
	public int updateShopToNoPass(int shopId) {
		String sql = "update shop set available = 0 where shopId=" + shopId;
		Connection connection = null;
		PreparedStatement statement = null;
		int b = 0;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			b = statement.executeUpdate();
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
					b = -1;
				} catch (SQLException e1) {
					b = -2;
				}
			} else {
				b = -3;
			}
		} finally {
			DBUtil.free(connection, statement, null);
		}
		return b;
	}

	public int updateShopToBlack(int shopId) {
		String sql = "update shop set available = -2 where shopId=" + shopId;
		Connection connection = null;
		PreparedStatement statement = null;
		int b = 0;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			b = statement.executeUpdate();
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
					b = -1;
				} catch (SQLException e1) {
					b = -2;
				}
			} else {
				b = -3;
			}
		} finally {
			DBUtil.free(connection, statement, null);
		}
		return b;
	}

	public int updateShopToWhite(int shopId) {
		String sql = "update shop set available = 1 where shopId=" + shopId;
		Connection connection = null;
		PreparedStatement statement = null;
		int b = 0;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			b = statement.executeUpdate();
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
					b = -1;
				} catch (SQLException e1) {
					b = -2;
				}
			} else {
				b = -3;
			}
		} finally {
			DBUtil.free(connection, statement, null);
		}
		return b;
	}

	public int updateShopAvailable(int shopId, int available) {
		String sql = "update shop set available = ? where shopId=?";
		Connection connection = null;
		PreparedStatement statement = null;
		int b = 0;
		try {
			connection = DBUtil.connect();
			statement = connection.prepareStatement(sql);
			statement.setInt(1, available);
			statement.setInt(2, shopId);
			b = statement.executeUpdate();
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
					b = -1;
				} catch (SQLException e1) {
					b = -2;
				}
			} else {
				b = -3;
			}
		} finally {
			DBUtil.free(connection, statement, null);
		}
		return b;
	}

	public int updateShopsAvailable(List<ChecksShopModel> checks) {
		int b = 0;
		for(int i = 0; i < checks.size(); i++){
			updateShopAvailable(checks.get(i).getShopId(), checks.get(i).getAvailable());
			ChecksDao checksDao = new ChecksDaoImpl();
			checksDao.delCheck(checks.get(i).getChecksId());
			b++;
		}
		return b;
	}
	

	// for insert into data in database
	public void addShopData(int i) {
		String sql = "insert into shop(shopId,MAccount,shopNumber,legalName,sPhone,"
				+ "address,shopName,signTime,available,sendTime,describe,"
				+ "startTime,endTime,icon,star,soldNum,status) "
				+ "values (shop_seq.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		int size = i + 30;
		Connection connection = null;
		PreparedStatement pst = null;
		try {
			connection = DBUtil.connect();
			for (  ; i < size; i++) {
				pst = connection.prepareStatement(sql);
				pst.setString(1, "account" + i);
				pst.setString(2, "shopNumber" + i);
				pst.setString(3, "legalName" + i);
				pst.setString(4, "sPhone" + i);
				pst.setString(5, "address" + i);
				pst.setString(6, "shopName" + i);
				pst.setString(7, "signTime" + i);
				pst.setInt(8, 0);
				pst.setString(9, "sendTime" + i);
				pst.setString(10, "describe" + i);
				pst.setString(11, "startTime" + i);
				pst.setString(12, "endTime" + i);
				pst.setString(13, "icon" + i);
				pst.setInt(14, 0);
				pst.setInt(15, 0);
				pst.setInt(16, 0);
				pst.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.free(connection, pst, null);
		}
		System.out.println("data was inserted");
	}
	//for insert into data in database MENU
	public void addMenuData(int i) {
		String sql = "insert into Menu(MenuID,menuname,price,star,soldnum,icon,shopID) "
				+ "values (menu_seq.nextval,?,?,?,?,?,?)";
		int size = i + 5;
		Connection connection = null;
		PreparedStatement pst = null;
		try {
			connection = DBUtil.connect();
			for (  ; i < size; i++) {
				pst = connection.prepareStatement(sql);
				pst.setString(1, "menuname" + i);
				pst.setDouble(2, 100.00);
				pst.setInt(3, 0);
				pst.setInt(4, 0);
				pst.setString(5, "icon" + i);
				pst.setInt(6,2);
			
				pst.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.free(connection, pst, null);
		}
		System.out.println("data was inserted");
	}
}
